Heuristic SQL Injection Detection for Suhosin

Thursday, December 28. 2006

Today I added a little simple SQL Injection Detection Heuristic to the development version of the Suhosin extension that can optionally log and block SQL queries. At the moment it is possible to log and/or block mysql(i) SQL queries that contain comments, comments that are not closed, queries with UNIONs or queries with multiple SELECT statements.

While this are very trivial checks they are very powerful and effective against a large number of SQL Injection attacks. Many PHP applications never have comments in their SQL statements, therefore any embedded comment and especially unclosed /* comments could be attempts to truncate the data after the injection point. The same is true for UNION and multiple SELECT statements (in subqueries). Both features are not used in the majority of open source PHP applications because they are not needed for the tasks or because compatibility with old MySQL versions was kept for a long time. Therefore an SQL Injection attack is very likely when either one is encountered.

Future versions of Suhosin will have a learning mode that learns the structure of allowed SQL queries and will log/block all violating queries. The release of the next Suhosin version that contains the simple heuristic and some bugfixes is planned for 1st January.

Posted by Stefan Esser in Security, PHP at 23:55

Comments (13)

Link to this post

Comments

Display comments as (Linear | Threaded)

#1 Ilia Alshanetsky wrote (Link) ()

UNIONS are starting to be quite common especially in MySQL code I've recently been reviewing. I hope that the option to block queries with them is a separate config directive.

posted on Thursday, December 28. 2006

#1.1 Stefan Esser wrote (Link) ()

Of course this are all separate config options. However the majority of open source products do not use UNION.

posted on Thursday, December 28. 2006

#1.1.1 Tobias Struckmeier ()

Hi Stefan,

it would be interesting if those features are enabled by default or not.
Can you give me information on that?

Cheers,
Tobias

posted on Thursday, December 28. 2006

#1.1.1.1 Stefan Esser wrote (Link) ()

Suhosin (unless you are using a beta/developer version) does not activate features by default that could break secure applications.

And because it is possible that secure applications use UNION or subSELECTS, or comments it is not activated.

And even if it is activated it is possible to disable the feature from .htaccess (and maybe even from ini_set in the release on 1st January).

posted on Thursday, December 28. 2006

#1.1.2 Maarten Manders wrote (Link) ()

UNION can be quite useful: http://www.mysqlperformanceblog.com/2006/08/14/mysql-followup-on-union-for-query-optimization-query-profiling/

posted on Friday, December 29. 2006

#1.1.2.1 Stefan Esser wrote (Link) ()

I am fully aware that UNION can have a huge impact on the performance of applications.

I used it myself to eliminate performance problems in 3rd party sites.

However if you look into Open Source PHP application you will have to look at many many many many to find just one application that uses UNION. All the usual suspects like phpBB, Wordpress, S9y,... do not need them.

Therefore it is a good idea to let the admin decide if he allows these things or not. Infact he can let it run with log only mode for a while to be sure and later he can activate the blocking, so that such queries are blocked. And if he does not want to block he can leave it activated for logging only and just send himself an email whenever such a query is executed. And infact the logging script could also postprocess the query and ignore legal ones...

posted on Friday, December 29. 2006

#2 nick wrote (Link) ()

Lemme start by saying I only recently started lurking in discussions of security concerns like SQL injection but never really commented on it. Also I've spent very little time coding against MySQL db's despite having spent the last 6 years writing PHP code. (all Oracle.)

It seems to me that removing the source of SQL injections would be the most effective way to deal with them. For me that means using only bind queries. So, I would imagine creating a security patch that hamstrings oci_parse() to fail when called with a query string containing quoted values. That'd force the developer to write queries using bind variables, use oci_bind_by_name(), and eliminating SQL injections as a possibility.

I don't know what the correlary would be in MySQL because as I understand it, MySQL doesn't have bind variables. Maybe a patch could force the user to emulate the behavior that PDO allows regarding prepared query strings with ? as placeholder for values.

Anyway. I'm sure I don't have all the angles. But if you want to prevent SQL injections, why even allow developers to write injectable SQL?

posted on Thursday, December 28. 2006

#2.1 Stefan Esser wrote (Link) ()

First of all mysql has bind variables if you use the mysqli extension.

Secondly Suhosin is not about forcing application developers to use other functions, but to help administrators of PHP servers to protect their servers against common problems in PHP and PHP applications. Therefore it is not Suhosin's job to stop the usage of function xyz.

And last but not least prepared statements are not a protection against SQL Injection. Prepared statements are meant for faster reuse of SQL queries that all follow the same syntax. It is true that when only bind parameters are used SQL Injection is impossible (if there is not a bug in the SQL engine), but SQL Injection is still possible when for example table names or order by statements are dynamically inserted. Additionally web applications often need dynamically different SQL Queries like ... field_xyz in (1,2,3,4) ...
Whenever such a construct is used, it is often firectly inserted into the statement before it is passed to the "preparation" process.

And don't tell me that these are unimportant cases. The cases mentioned are exactly the places that contain SQL Injection problems in otherwise safe applications, because there are no escaping functions for table names, order by statements or value lists. And afaik there are also no bind parameters for these cases. (At least not in MySQL)

posted on Thursday, December 28. 2006

#2.1.1 nick wrote (Link) ()

The use case of variable field or table names is certainly valid - off the top of my head I'm pretty sure you can't bind values to those parts of the query in Oracle or Postgres. But then again, I'm sure I've never tried it.

posted on Thursday, December 28. 2006

#3 JW ()

Interesting.

Wondering if its possible to fingerprint SQL. Possibly by removing any constants and/or whitespace, and hashing, then could use that as a whitelist.

Also perhaps functions like mysqli_multi_query(), which allow multiple sql statements to be executed, and therefore more flexible when it comes to SQL injection may need to be limited.

posted on Friday, December 29. 2006

#4 Marten Veldthuis wrote (Link) ()

Upon reading this I had an idea for another kind of SQL Injection prevention from the Suhosin side, not sure if it's possible though. Would it be possible to combine knowledge about the querystring and about queries being executed to (My)SQL. Meaning, you might be able to detect whether SQL keywords are appearing in the querystring, which are also subsequently being passed into the SQL query.

Again, not sure about the feasibility, but I'd love to hear.

posted on Friday, December 29. 2006

#5 Dave ()

MySQL comments are used to differentiate between different versions of MySQL...

posted on Sunday, December 31. 2006

#5.1 Stefan Esser wrote (Link) ()

And your point is?

You know SQL Queries are for retrieving and manipulating Data... But this statement is also irrelevant.

The majority of Open Source Applications does not have comments in their SQL Queries and therefore the admin can configure Suhosin for these applications to disallow comments.

posted on Sunday, December 31. 2006

Add Comment

Name:

Email:

Homepage:

In reply to:

Comment:
Standard emoticons like :-) and ;-) are converted to images.

Remember Information? Subscribe to this entry
Submitted comments will be subject to moderation before being displayed.

Calendar